---
title: "Mysql Json DataType"
date: 2019-8-15
categories:
- mysql
tags:
---

<div id="content">
<div id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#org71fec6c">Functions</a>
<ul>
<li><a href="#org9ff8e3f">JSON_CONTAINS</a></li>
<li><a href="#org2ae98e0">JSON_EXTRACT</a></li>
<li><a href="#org1a682cb">JSON_SEARCH</a>
<ul>
<li><a href="#orgc181c7c">Parameters</a></li>
<li><a href="#orgfb208b6">Note</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="#orgd0b55fe">Snippets</a>
<ul>
<li><a href="#org92da47e">Search Json Array</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div class="outline-2" id="outline-container-org71fec6c">
<h2 id="org71fec6c">Functions</h2>
<div class="outline-text-2" id="text-org71fec6c">
<p>
Sample:
</p>
<div class="org-src-container">
<pre class="src src-json">{  
  "int": 200,
  "str": "200",
  "int_list": [200, 201],
  "str_list": ["200", "201"],
  "args": {
    "cmd": {
      "str_list": ["60","61"],
      "int_list": [60, 61]
    }
  }
}
</pre>
</div>
</div>
<div class="outline-3" id="outline-container-org9ff8e3f">
<h3 id="org9ff8e3f">JSON_CONTAINS</h3>
<div class="outline-text-3" id="text-org9ff8e3f">
<p>
JSON_CONTAINS(target, candidate[, path])
判断target中是否包含candidate，如果指定了path，则在指定的路径中搜索。
返回0和1。
</p>
<p>
path中不能包含 '<b>' '*</b>' 等通配符。
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">set</span> @target=<span style="font-style: italic;">'{  "int": 200, "str": "200", "int_list": [200, 201], "str_list": ["200", "201"], "args": { "cmd": { "str_list": ["60","61"], "int_list": [60, 61] } } }'</span>

# Test {"str": "200"}
<span style="font-weight: bold;">select</span> json_contains(@target, <span style="font-style: italic;">'{"str":"200"}'</span>);
# 1

# Test {"<span style="font-weight: bold; text-decoration: underline;">int</span>": 200}
<span style="font-weight: bold;">select</span> json_contains(@target, <span style="font-style: italic;">'{"int":200}'</span>);
# 1

# Test {"str_list": ["200"]}
<span style="font-weight: bold;">select</span> json_contains(@target, <span style="font-style: italic;">'{"str_list":["200"]}'</span>);
# 1

# Test {"str_list": ["202"]}
<span style="font-weight: bold;">select</span> json_contains(@target, <span style="font-style: italic;">'{"str_list":["202"]}'</span>);
# 0

# Test {"int_list": [200]}
<span style="font-weight: bold;">select</span> json_contains(@target, <span style="font-style: italic;">'{"int_list": [200]}'</span>)
# 1

# Test {"int_list": [202]}
<span style="font-weight: bold;">select</span> json_contains(@target, <span style="font-style: italic;">'{"int_list": [202]}'</span>)
# 0

# Test {"int_list": 200}
<span style="font-weight: bold;">select</span> json_contains(@target, <span style="font-style: italic;">'{"int_list": 200}'</span>)
# 1

# Test {"int_list": 61} <span style="font-weight: bold;">at</span> <span style="font-weight: bold;">path</span> args.cmd
<span style="font-weight: bold;">select</span> json_contains(@target, <span style="font-style: italic;">'{"int_list": 61}'</span>, <span style="font-style: italic;">'$.args.cmd'</span>);
# 1


</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org2ae98e0">
<h3 id="org2ae98e0">JSON_EXTRACT</h3>
<div class="outline-text-3" id="text-org2ae98e0">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">select</span> json_extract(@target, <span style="font-style: italic;">'$**.int_list'</span>);
# [[200, 201], [60, 61]]

# 配合json_contains函数使用，判断结果中是否包含指定的元素，解决json_search不能搜索<span style="font-weight: bold; text-decoration: underline;">int</span>值的问题：
<span style="font-weight: bold;">select</span> json_contains(json_extract(@target, <span style="font-style: italic;">'$**.int_list'</span>), <span style="font-style: italic;">'62'</span>);

</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org1a682cb">
<h3 id="org1a682cb">JSON_SEARCH</h3>
<div class="outline-text-3" id="text-org1a682cb">
<pre class="example">
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
</pre>
<p>
找到 json_doc 中指定路径下的的内容匹配 search_str 的文档的路径
</p>
</div>
<div class="outline-4" id="outline-container-orgc181c7c">
<h4 id="orgc181c7c">Parameters</h4>
<div class="outline-text-4" id="text-orgc181c7c">
</div>
<ul class="org-ul">
<li><a id="org18f0863"></a>one_or_all<br/>
<ul class="org-ul">
<li><a id="org5aa33c4"></a>'one': 只查找一个，找到就返回找到的json路径<br/></li>
<li><a id="org0bf23e5"></a>'all': 返回找到的所有路径（不包含重复的）。如果有多个匹配的路径，则用一个数组包装（数组没有顺序）<br/></li>
</ul>
</li>
<li><a id="org8f533ff"></a>search_str<br/>
<div class="outline-text-5" id="text-org8f533ff">
<p>
查找字符串， '%', '<span class="underline">'这两个字符类似于 like ，而 '</span>' 只匹配一个字符（'%'匹配多个）
</p>
</div>
</li>
<li><a id="org35ba670"></a>escape_char<br/>
<div class="outline-text-5" id="text-org35ba670">
<p>
转义字符，如果为NULL，则默认为 '\'
</p>
</div>
</li>
</ul>
</div>
<div class="outline-4" id="outline-container-orgfb208b6">
<h4 id="orgfb208b6">Note</h4>
<div class="outline-text-4" id="text-orgfb208b6">
<p>
json_search函数无法搜索到int型的值。
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">SET</span> @j = <span style="font-style: italic;">'[3, 2, 1]'</span>;
<span style="font-weight: bold;">SELECT</span> json_search(@j, <span style="font-style: italic;">'one'</span>, 2);
# <span style="font-weight: bold;">NULL</span>
</pre>
</div>
<blockquote>
<p>
<a href="https://stackoverflow.com/questions/40356672/mysql-triggers-json-search-an-integer-value-in-a-json-array-of-integers">https://stackoverflow.com/questions/40356672/mysql-triggers-json-search-an-integer-value-in-a-json-array-of-integers</a>
<a href="https://dev.mysql.com/worklog/task/?id=7909">https://dev.mysql.com/worklog/task/?id=7909</a>
</p>
</blockquote>
</div>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-orgd0b55fe">
<h2 id="orgd0b55fe">Snippets</h2>
<div class="outline-text-2" id="text-orgd0b55fe">
</div>
<div class="outline-3" id="outline-container-org92da47e">
<h3 id="org92da47e">Search Json Array</h3>
<div class="outline-text-3" id="text-org92da47e">
<pre class="example">
https://stackoverflow.com/questions/36249828/how-to-search-json-array-in-mysql
</pre>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">select</span> json_search(<span style="font-style: italic;">'{"standard_order_list": [{"standard_order_sn": "STO-23475384"}]}'</span>, 
<span style="font-style: italic;">'all'</span>, <span style="font-style: italic;">'%23475384%'</span>, <span style="font-weight: bold;">null</span>, <span style="font-style: italic;">'$.standard_order_list[*].standard_order_sn'</span>);
# 用于查询：
<span style="font-weight: bold;">select</span> * <span style="font-weight: bold;">from</span> t_tb <span style="font-weight: bold;">where</span> json_search(...) <span style="font-weight: bold;">is</span> <span style="font-weight: bold;">not</span> <span style="font-weight: bold;">null</span>;
</pre>
</div>
</div>
</div>
</div>
</div>
<div class="status" id="postamble">
<p class="date">Date: 2019-8-15</p>
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer">Validate</a></p>
</div>
